package com.foreknow.demo15;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * DriverManager类
 * Connection接口 表示获取到的连接
 * Statement接口 用于执行静态SQL语句的对象
 * ResultSet接口 用于保存查询到的数据
 */
public class DBUtil {
    Connection conn;
    PreparedStatement stmt;
    ResultSet rs;
    /**
     * 连接数据库的方法
     * @param
     */
    public Connection getConnection() throws ClassNotFoundException, SQLException {
        //1. 加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获取连接
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/basic17?useUnicode=true&characterEncoding=utf8&useSSL=true","root","123456");
        return conn;
    }

    /**
     * 查询的方法
     * select * from employee where username=? and password=?
     */
    public ResultSet query(String sql,Object...values) throws SQLException {
        stmt =  conn.prepareStatement(sql);
        for(int i = 0;i<values.length;i++) {
            stmt.setObject(i+1,values[i]);
        }
        rs = stmt.executeQuery();
        return rs;
    }

    /**
     * DML(insert delete update)
     *insert into employee(username,password) values(?,?)
     */
    public int dml(String sql,Object...values) throws SQLException {
        stmt =  conn.prepareStatement(sql);
        for(int i = 0;i<values.length;i++) {
            stmt.setObject(i+1,values[i]);
        }
        return stmt.executeUpdate() ;
    }

    /**
     * 关闭资源
     *
     */
    public void closed() throws SQLException {
        if(rs!=null) {
            rs.close();
        }
        if(stmt!=null) {
            stmt.close();
        }
        if(conn!=null) {
            conn.close();
        }


    }

    public static void main(String[] args) {
        DBUtil dbUtil = new DBUtil();
        try {
            //1.连接数据库
            dbUtil.getConnection();
            //2.完成insert
            //            int isRigth = dbUtil.dml("insert into employee(username,password) values('yamaha','11121212')");
            //            if(isRigth==1) {
            //                System.out.println("insert success......");
            //            }else {
            //                System.out.println("insert error......");
            //            }
            //3. 查询
            ResultSet rs = dbUtil.query("select * from employee where username=? and password=?","fender","123456");
            List<Employee> list = new ArrayList<>();
            while (rs.next()) {
                Employee employee = new Employee();
                employee.setId(rs.getInt("ID"));
                employee.setUsername(rs.getString("USERNAME"));
                employee.setPassword(rs.getString("PASSWORD"));
                list.add(employee);
            }
            for (Employee e:list) {
                System.out.println(e.getUsername());
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }


//        try {
//            //1. 加载驱动程序
//            Class.forName("com.mysql.jdbc.Driver");
//            //2. 获取连接
//            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/basic17?useUnicode=true&characterEncoding=utf8&useSSL=true","root","123456");
//            //3. 获取Statement
//            Statement statement = conn.createStatement();
//            //4. 执行查询
//            ResultSet rs = statement.executeQuery("select * from employee");
//            List<Employee> list = new ArrayList<>();
//            while (rs.next()) {
//                Employee employee = new Employee();
//                employee.setId(rs.getInt("ID"));
//                employee.setUsername(rs.getString("USERNAME"));
//                employee.setPassword(rs.getString("PASSWORD"));
//                list.add(employee);
//            }
//            for (Employee e:list) {
//                System.out.println(e.getUsername());
//            }
//            //5.关闭资源
//            rs.close();
//            statement.close();
//            conn.close();
//
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
    }
}
